package com.mmy.dao.scenic;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.scenic.TScenicPlan;

public interface TScenicPlanRepository extends JpaRepository<TScenicPlan, Integer> {
	/**
	 * 获取计划单最大值
	 * 
	 * @return 返回值说明 最大计划单编码
	 * @author 创建人 dingxd
	 * @date 创建日期 2020.11.16
	 */
	@Query(value = "SELECT MAX(iPlanID) FROM tScenicPlan WHERE LEFT(iPlanID,2) = RIGHT(YEAR(NOW()),2)", nativeQuery = true)
	Object getMaxPlanID();

	/**
	 * 创建景区计划
	 * 
	 * @author 创建人hanxl
	 * @date 创建日期 2020.12.28
	 */
	@Transactional
	@Modifying
	@Query(value = "INSERT INTO tScenicPlan(iPlanID, cTicketName, iScenicID, dStartDate, dEndDate, fFloor, fPeer, fPrice, iNumber, dCreateDate, cCreator, dModifyDate, cReviser, cMemo, cSpare1, cSpare2, cSpare3, cSpare4, cSpare5) VALUES (:planID, NULL, 0, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)", nativeQuery = true)
	void save(@Param("planID") int planID);

	/**
	 * 批量删除
	 * 
	 * @author 创建人hanxl
	 * @date 创建日期 2020.12.28
	 */
	@Transactional
	@Modifying
	@Query(value = "delete from tScenicPlan where iPlanID in (:lstPlanID)", nativeQuery = true)
	void delScenicPlan(@Param("lstPlanID") List<Integer> lstPlanID);

	/**
	 * 修改景区计划时需要查询改景区信息
	 * 
	 * @author 创建人hanxl
	 * @date 创建日期 2020.12.28
	 */
	@Query(value = "select *  from tScenicPlan where iPlanID = :iPlanID", nativeQuery = true)
	TScenicPlan findByPlanID(@Param("iPlanID") int iPlanID);

	/* 查找每个景区最小的价格 hanxl */
	@Transactional
	@Modifying
	@Query(value = "select s.* from (select min(fPrice) as minpr,iScenicID from tScenicPlan  GROUP BY iScenicID) s1,tScenicPlan s where s1.iScenicID = s.iScenicID and s1.minpr=s.fPrice", nativeQuery = true)
	List<TScenicPlan> findMinPrice();

	/**
	 * 前台景区菜单首页显示查询计划日期大于当前日期的景区ID
	 * 
	 * @author 创建人 hanxl
	 * @param strScenicPrice2
	 * @param strScenicPrice1
	 * @date 创建日期 2021.01.13
	 */
	@Query(value = "select distinct iScenicID from  tScenicPlan where now() between dStartDate and dEndDate and (:strScenicPrice1 = \"\" or :strScenicPrice1<=fPrice) and (:strScenicPrice2 = \"\" or :strScenicPrice2>=fPrice) ", nativeQuery = true)
	List<Integer> getScenicID(@Param("strScenicPrice1") String strScenicPrice1,
			@Param("strScenicPrice2") String strScenicPrice2);

	/**
	 * 通过景区ID查询所有计划ID
	 * 
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.01.22
	 */
	@Query(value = "select iPlanID from  tScenicPlan where iScenicID in(:lstScenicID) ", nativeQuery = true)
	List<Integer> getBylstScenicID(@Param("lstScenicID") List<Integer> lstScenicID);

}
